﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Util;
using Model;
using IDAL;

namespace DAL
{
    public class UserDao : IUserDao
    {
        DBHelpr dBHelpr = new DBHelpr();

        public int InsertUser(User user)
        {
            string sql = "INSERT INTO SYS_USER_INFO (USER_ID,USER_NO,USER_NAME_CH,CREATE_USERID,CREATE_DATE,VALID_DATE)";
            sql += " VALUES(SYS_USER_INFO_USER_ID.NEXTVAL,:userNo,:userName,0,SYSDATE,TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'))";
            OracleParameter[] pms = new OracleParameter[]
            {
                new OracleParameter(":userNo",user.UserNo),
                new OracleParameter(":userName",user.UserName),
            };
            return DBHelpr.ExecuteNonQuery(sql, pms);
        }
        public int UpdateUserByUserNo(User user)
        {
            string sql = " UPDATE SYS_USER_INFO SET USER_NAME_CH = :userName  WHERE USER_NO = :userNo";

            OracleParameter[] pms = new OracleParameter[]
            {
                new OracleParameter(":userNo",user.UserNo),
                new OracleParameter(":userName",user.UserName),
            };
            return DBHelpr.ExecuteNonQuery(sql, pms);
        }
        public int DeleteUserByUserNo(User user)
        {
            string sql = " DELETE FROM  SYS_USER_INFO WHERE USER_NO=:userNo";

            OracleParameter[] pms = new OracleParameter[]
            {
                new OracleParameter(":userNo",user.UserNo),
            };
            return DBHelpr.ExecuteNonQuery(sql, pms);
        }
        /**
         * 查询方式一：
         * 1.BY条件查询并返回结果，ExecuteReader返回 OracleDataReader对象；
         * 在读取数据的时候需要一直连接数据库。也就是说conn连接要一直打开。
         */
        public User GetUserByNameNo(string nameNo)
        {
            //1.SQL语句
            string sql = "select USER_NO,USER_NAME_CH from sys_user_info where user_no = :nameNo";
            //2.填充参数
            OracleParameter[] pms = new OracleParameter[]
            {
                new OracleParameter(":nameNo",nameNo),
            };
            User user = new User();
            //3.查询操作，返回OracleDataReader数据对象，须读取数据，存储到对象变量中。
            using (OracleConnection conn = new OracleConnection(DBHelpr.connstr))
            {
                conn.Open();
                OracleDataReader dr = DBHelpr.ExecuteReader(sql, pms);
                //4.读取数据，存储到对象变量中。
                while (dr.Read())
                {
                    user.UserName = dr["USER_NAME_CH"].ToString();
                    user.UserNo = dr["USER_NO"].ToString();
                }
            }
            return user;
        }
        /**
        * 查询方式二：
        * 1.BY条件查询并返回结果，ExecuteScalar返回 Object对象；
        * 查询结果集只返回第一行第一列的数据
        */
        public Object GetUsersByDeptNo(string deptNo)
        {
            //1.SQL语句
            string sql = "SELECT COUNT(0)";
            sql += " FROM SYS_USER_INFO A,BAS_EMPLOYEE_INFO B,BAS_DEPT_INFO C";
            sql += " WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID";
            sql += " AND B.DEPT_ID = C.DEPT_ID";
            sql += " AND SUBSTR(A.USER_NO,1,1)!='#'";
            sql += " AND C.STATUS = 'E'";
            sql += " AND C.DEPT_NO = :deptNo";
            //2.填充参数
            OracleParameter[] pms = new OracleParameter[]
            {
                new OracleParameter(":deptNo",deptNo),
            };
            //3.查询操作，返回Object对象
            using (OracleConnection conn = new OracleConnection(DBHelpr.connstr))
            {
                conn.Open();
                return DBHelpr.ExecuteScalar(sql, pms);
            }
        }
        /**
       * 查询方式三：
       * 1.BY条件查询并返回结果，OracleDataAdapter返回 DataTable对象；
       * 查询结果集返回一个table的数据
       */
        public DataTable GetUserTableByUserNo(User user)
        {
            //1.SQL语句
            string sql = "select USER_NO,USER_NAME_CH from sys_user_info where user_no = :nameNo";
            //2.填充参数
            OracleParameter[] pms = new OracleParameter[]
            {
                new OracleParameter(":nameNo",user.UserNo),
            };
            //3.查询操作，返回DataTable对象
            using (OracleConnection conn = new OracleConnection(DBHelpr.connstr))
            {
                conn.Open();
                return DBHelpr.DataTable(sql, pms);
            }
        }
        /**
      * 查询方式四：
      * 1.BY条件查询并返回结果，OracleDataAdapter返回 DataSet对象；
      * 查询结果集返回一个set的可存储多个table的数据
      */
        public DataSet GetUserSetByUserNo(User user)
        {
            //1.SQL语句
            string sql = "select USER_NO,USER_NAME_CH from sys_user_info where user_no = :nameNo";
            //2.填充参数
            OracleParameter[] pms = new OracleParameter[]
            {
                new OracleParameter(":nameNo",user.UserNo),
            };
            //3.查询操作，返回DataTable对象
            using (OracleConnection conn = new OracleConnection(DBHelpr.connstr))
            {
                conn.Open();
                return DBHelpr.DataSet(sql, pms);
            }
        }
    }
}
